package com.artup.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import com.artup.pojo.Address;

@Repository
public interface AddressDao {
	/**
	 * 添加地址
	 * @param address 【地址】
	 * @throws Exception
	 */
	@Insert( { "INSERT INTO bud_address(db_id, user_db_id, NAME, mobile, province, city, region, address, main_addr, CREATED_DT) "
			+ "VALUES(#{id, javaType=String, jdbcType=VARCHAR}, #{passportId, javaType=Integer, jdbcType=INTEGER}, #{name, javaType=String, jdbcType=VARCHAR}, #{mobileCode, javaType=String, jdbcType=VARCHAR}, #{provinceId, javaType=Long, jdbcType=BIGINT}, #{cityId, javaType=Long, jdbcType=BIGINT}, #{districtId, javaType=Long, jdbcType=BIGINT}, #{detail, javaType=String, jdbcType=VARCHAR}, #{isDefault, javaType=String, jdbcType=VARCHAR}, NOW())" } )
	void insertAddress(Address address) throws Exception;
	
	/**
	 * 根据【ID】更新【地址】
	 * @param address 【地址】
	 * @throws Exception
	 */
	@Update( { "UPDATE bud_address a"
			+ " SET a.name = #{name, javaType=String, jdbcType=VARCHAR}, a.mobile = #{mobileCode, javaType=String, jdbcType=VARCHAR}, a.province = #{provinceId, javaType=Long, jdbcType=BIGINT}, a.city = #{cityId, javaType=Long, jdbcType=BIGINT}, a.region = #{districtId, javaType=Long, jdbcType=BIGINT}, a.address = #{detail, javaType=String, jdbcType=VARCHAR}, a.main_addr = #{isDefault, javaType=String, jdbcType=VARCHAR}"
			+ " WHERE a.db_id = #{id, javaType=String, jdbcType=VARCHAR}" } )
	void updateAddressById(Address address) throws Exception;
	
	/**
	 * 根据【ID】删除【地址】
	 * @param id 【ID】
	 * @throws Exception
	 */
	@Delete( value = { "DELETE FROM bud_address WHERE db_id = #{id, javaType=String, jdbcType=VARCHAR}" } )
	void deleteAddressById(String id) throws Exception;
	
	/**
	 * 根据【ID】查询【地址】
	 * @param id 【ID】
	 * @return 【地址】
	 * @throws SQLException
	 */
	@Select( value = { "SELECT a.db_id AS id, a.name, a.mobile mobileCode, a.province AS provinceId, a.city AS cityId, a.region AS districtId, a.address AS detail FROM bud_address a WHERE a.db_id = #{id, javaType=String, jdbcType=VARCHAR}" } )
	Address selectAddressById(String id) throws SQLException;

	/**
	 * 根据【通行证ID】查询【默认地址】
	 * @param id 【ID】
	 * @return 【地址】
	 * @throws SQLException
	 */
	@Select( value = { "SELECT a.db_id AS id, a.name, a.mobile mobileCode, a.user_db_id AS passportId, a.province AS provinceId, a.city AS cityId, a.region AS districtId, a.address AS detail FROM bud_address a WHERE a.main_addr = 'Y' AND a.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER} " } )
	Address selectDefaultAddressByPassportId(int passportId) throws SQLException;

	/**
	 * 根据【通行证ID】查询【地址列表】
	 * @param passportId 【通行证ID】
	 * @return 【地址列表】
	 * @throws SQLException
	 */
	@Select( value = { "SELECT a.db_id AS id, a.user_db_id AS passportId, a.name, a.mobile AS mobileCode, a.province AS provinceId, a.city AS cityId, a.region AS districtId, a.address AS detail, a.main_addr AS isDefault FROM bud_address a WHERE a.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER}" } )
	List<Address> selectAddressListByPassportId(int passportId) throws SQLException;

	/**
	 * 根据【IDs】查询【地址列表】
	 * @param ids 【IDs】
	 * @return 【地址列表】
	 * @throws SQLException
	 */
	@Select( value = { "<script>"
			+ "SELECT a.db_id AS id, a.name, a.mobile AS mobileCode, a.province AS provinceId, a.city AS cityId, a.region AS districtId, a.address AS detail FROM bud_address a WHERE a.db_id IN "
				+ "<foreach collection='ids' item='id' open='(' close=')' separator=','>"
					+ "#{id, javaType=String, jdbcType=VARCHAR}"
				+ "</foreach>"
			+ " </script>" } )
	List<Address> selectAddressListByIds(@Param( "ids" ) String [] ids) throws SQLException;
	
	/**
	 * 根据【通行证ID】更新当前会员名下的【地址】为非默认
	 * @param id 【通行证ID】
	 * @throws SQLException
	 */
	@Update( value = { "UPDATE bud_address a SET a.main_addr = 'N' WHERE a.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER}" } )
	void updateAddressNotDefaultByPassportId(int passportId) throws SQLException;

	/**
	 * 根据【ID】更新当前会员名下的【地址】为非默认
	 * @param id 【ID】
	 * @throws SQLException
	 */
	@Update( value = { "UPDATE bud_address a SET a.main_addr = 'N' WHERE a.user_db_id IN ( SELECT t1.user_db_id FROM ( SELECT a2.user_db_id FROM bud_address a2 WHERE a2.db_id = #{id, javaType=String, jdbcType=VARCHAR} ) t1 )" } )
	void updateAddressNotDefaultById(String id) throws SQLException;

	/**
	 * 根据【ID】更新【地址】为默认
	 * @param id 【ID】
	 * @throws SQLException
	 */
	@Update( value = { "UPDATE bud_address a SET a.main_addr = 'Y' WHERE a.db_id = #{id, javaType=String, jdbcType=VARCHAR} " } )
	void updateAddressDefaultById(String id) throws SQLException;
}
